package com.kexio.auth.mybatis;

import java.util.Arrays;
import java.util.List;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

/**
 * SQL安全验证器
 * 
 * 用于验证SQL输入的安全性，防止SQL注入攻击。
 * 提供多层防护：输入验证、关键字检测、模式匹配等。
 * 
 * @author Kexio Team
 * @since 1.0.0
 */
public class SqlSecurityValidator {
    
    private static final Logger logger = LoggerFactory.getLogger(SqlSecurityValidator.class);
    
    /**
     * SQL关键字列表（黑名单）
     */
    private static final List<String> SQL_KEYWORDS = Arrays.asList(
        "SELECT", "INSERT", "UPDATE", "DELETE", "DROP", "CREATE", "ALTER", "TRUNCATE",
        "UNION", "JOIN", "WHERE", "FROM", "HAVING", "GROUP", "ORDER", "EXEC", "EXECUTE",
        "SCRIPT", "DECLARE", "CURSOR", "PROCEDURE", "FUNCTION", "TRIGGER", "INDEX",
        "VIEW", "GRANT", "REVOKE", "COMMIT", "ROLLBACK", "SAVEPOINT"
    );
    
    /**
     * 危险SQL模式（正则表达式）
     */
    private static final List<Pattern> DANGEROUS_PATTERNS = Arrays.asList(
        Pattern.compile(".*(['\";]|--|\\/\\*|\\*\\/).*", Pattern.CASE_INSENSITIVE),
        Pattern.compile(".*\\b(union|select|insert|update|delete|drop|create|alter)\\b.*", Pattern.CASE_INSENSITIVE),
        Pattern.compile(".*\\b(exec|execute|sp_|xp_)\\b.*", Pattern.CASE_INSENSITIVE),
        Pattern.compile(".*\\b(script|javascript|vbscript)\\b.*", Pattern.CASE_INSENSITIVE),
        Pattern.compile(".*(<|>|&lt;|&gt;|%3c|%3e).*", Pattern.CASE_INSENSITIVE)
    );
    
    /**
     * 允许的字符模式
     */
    private static final Pattern ALLOWED_IDENTIFIER_PATTERN = Pattern.compile("^[a-zA-Z_][a-zA-Z0-9_]*$");
    
    /**
     * 最大输入长度限制
     */
    private static final int MAX_IDENTIFIER_LENGTH = 64;
    private static final int MAX_VALUE_LENGTH = 255;
    private static final int MAX_CONDITION_LENGTH = 1000;
    
    /**
     * 验证标识符（表名、列名等）
     * 
     * @param identifier 标识符
     * @return 验证通过返回true
     * @throws SecurityException 验证失败时抛出
     */
    public static boolean validateIdentifier(String identifier) {
        if (!StringUtils.hasText(identifier)) {
            throw new SecurityException("Identifier cannot be null or empty");
        }
        
        // 长度检查
        if (identifier.length() > MAX_IDENTIFIER_LENGTH) {
            throw new SecurityException("Identifier too long: " + identifier.length() + " > " + MAX_IDENTIFIER_LENGTH);
        }
        
        // 字符模式检查
        if (!ALLOWED_IDENTIFIER_PATTERN.matcher(identifier).matches()) {
            throw new SecurityException("Invalid identifier format: " + identifier);
        }
        
        // SQL关键字检查
        if (containsSqlKeywords(identifier)) {
            throw new SecurityException("Identifier contains SQL keywords: " + identifier);
        }
        
        logger.debug("Identifier validation passed: {}", identifier);
        return true;
    }
    
    /**
     * 验证值（用户输入的数据值）
     * 
     * @param value 值
     * @return 验证通过返回true
     * @throws SecurityException 验证失败时抛出
     */
    public static boolean validateValue(String value) {
        if (value == null) {
            return true; // null值允许
        }
        
        // 长度检查
        if (value.length() > MAX_VALUE_LENGTH) {
            throw new SecurityException("Value too long: " + value.length() + " > " + MAX_VALUE_LENGTH);
        }
        
        // 危险模式检查
        for (Pattern pattern : DANGEROUS_PATTERNS) {
            if (pattern.matcher(value).matches()) {
                logger.warn("Dangerous pattern detected in value: {}", value);
                throw new SecurityException("Value contains dangerous patterns: " + value);
            }
        }
        
        logger.debug("Value validation passed: {}", value);
        return true;
    }
    
    /**
     * 验证自定义SQL条件
     * 
     * @param condition 自定义条件
     * @return 验证通过返回true
     * @throws SecurityException 验证失败时抛出
     */
    public static boolean validateCustomCondition(String condition) {
        if (!StringUtils.hasText(condition)) {
            return true; // 空条件允许
        }
        
        // 长度检查
        if (condition.length() > MAX_CONDITION_LENGTH) {
            throw new SecurityException("Condition too long: " + condition.length() + " > " + MAX_CONDITION_LENGTH);
        }
        
        // 危险模式检查
        for (Pattern pattern : DANGEROUS_PATTERNS) {
            if (pattern.matcher(condition).matches()) {
                logger.warn("Dangerous pattern detected in condition: {}", condition);
                throw new SecurityException("Condition contains dangerous patterns: " + condition);
            }
        }
        
        // 检查是否包含过多的SQL关键字（可能是复杂攻击）
        long keywordCount = SQL_KEYWORDS.stream()
            .mapToLong(keyword -> countOccurrences(condition.toUpperCase(), keyword))
            .sum();
            
        if (keywordCount > 3) {
            logger.warn("Too many SQL keywords in condition: {} (count: {})", condition, keywordCount);
            throw new SecurityException("Condition contains too many SQL keywords: " + condition);
        }
        
        logger.debug("Custom condition validation passed: {}", condition);
        return true;
    }
    
    /**
     * 安全转义字符串
     * 
     * @param input 输入字符串
     * @return 转义后的字符串
     */
    public static String escapeString(String input) {
        if (input == null) {
            return null;
        }
        
        return input
            // 转义单引号
            .replace("'", "''")
            // 转义双引号
            .replace("\"", "\"\"")
            // 转义反斜杠
            .replace("\\", "\\\\")
            // 移除SQL注释符
            .replace("--", "")
            .replace("/*", "")
            .replace("*/", "")
            // 移除分号
            .replace(";", "");
    }
    
    /**
     * 清理并验证输入值
     * 
     * @param input 输入值
     * @return 清理后的安全值
     */
    public static String sanitizeInput(String input) {
        if (input == null) {
            return null;
        }
        
        // 先进行基本清理
        String cleaned = input.trim()
            // 移除多余空格
            .replaceAll("\\s+", " ")
            // 移除控制字符
            .replaceAll("[\\x00-\\x1F\\x7F]", "");
        
        // 然后进行安全转义
        cleaned = escapeString(cleaned);
        
        // 最后验证清理后的值
        validateValue(cleaned);
        
        return cleaned;
    }
    
    /**
     * 检查字符串是否包含SQL关键字
     * 使用词边界匹配，避免误判合法字段名（如create_by不应被CREATE关键字匹配）
     */
    private static boolean containsSqlKeywords(String input) {
        String upperInput = input.toUpperCase();
        return SQL_KEYWORDS.stream()
            .anyMatch(keyword -> {
                // 使用词边界正则表达式，确保是完整的关键字匹配
                String pattern = "\\b" + keyword + "\\b";
                return upperInput.matches(".*" + pattern + ".*");
            });
    }
    
    /**
     * 计算子字符串在字符串中出现的次数
     */
    private static long countOccurrences(String text, String substring) {
        if (text == null || substring == null || substring.isEmpty()) {
            return 0;
        }
        
        int count = 0;
        int index = 0;
        while ((index = text.indexOf(substring, index)) != -1) {
            count++;
            index += substring.length();
        }
        return count;
    }
    
    /**
     * 验证表别名是否安全
     * 
     * @param tableAlias 表别名（格式：table.column 或 alias.column）
     * @return 验证通过返回true
     */
    public static boolean validateTableAlias(String tableAlias) {
        if (!StringUtils.hasText(tableAlias)) {
            throw new SecurityException("Table alias cannot be null or empty");
        }
        
        // 处理表别名格式：table.column
        String[] parts = tableAlias.split("\\.");
        if (parts.length > 2) {
            throw new SecurityException("Invalid table alias format: " + tableAlias);
        }
        
        // 验证每个部分
        for (String part : parts) {
            validateIdentifier(part.trim());
        }
        
        return true;
    }
    
    /**
     * 生成安全的SQL片段用于日志记录
     * 
     * @param sql SQL语句
     * @return 安全的SQL片段（敏感信息被脱敏）
     */
    public static String sanitizeForLogging(String sql) {
        if (sql == null) {
            return null;
        }
        
        // 截断过长的SQL
        String truncated = sql.length() > 200 ? sql.substring(0, 200) + "..." : sql;
        
        // 脱敏敏感信息
        return truncated
            .replaceAll("'[^']*'", "'***'")  // 替换字符串常量
            .replaceAll("\\b\\d{11,}\\b", "***")  // 替换可能的敏感数字
            .replaceAll("password\\s*[=:]\\s*[^\\s]+", "password=***");  // 替换密码
    }
}
